package org.groupg.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class ExcelConverter {

    // 将 XLSX 转换为 XLS (保留样式和数据)
    public static void convertXLSXtoXLS(File inputFile, File outputFile) throws IOException {
        try (Workbook srcWorkbook = WorkbookFactory.create(inputFile);
             Workbook destWorkbook = new HSSFWorkbook()) {
            copySheetsWithStyles(srcWorkbook, destWorkbook);
            try (OutputStream os = new FileOutputStream(outputFile)) {
                destWorkbook.write(os);
            }
        }
    }

    // 将 XLS 转换为 XLSX (保留样式和数据)
    public static void convertXLStoXLSX(File inputFile, File outputFile) throws IOException {
        try (Workbook srcWorkbook = WorkbookFactory.create(inputFile);
             Workbook destWorkbook = new XSSFWorkbook()) {
            copySheetsWithStyles(srcWorkbook, destWorkbook);
            try (OutputStream os = new FileOutputStream(outputFile)) {
                destWorkbook.write(os);
            }
        }
    }

    // 复制工作表（含样式）
    private static void copySheetsWithStyles(Workbook src, Workbook dest) {
        for (int i = 0; i < src.getNumberOfSheets(); i++) {
            Sheet srcSheet = src.getSheetAt(i);
            Sheet destSheet = dest.createSheet(srcSheet.getSheetName());

            // 复制列宽
            copyColumnWidths(srcSheet, destSheet);

            // 复制合并单元格
            copyMergedRegions(srcSheet, destSheet);

            // 复制行和单元格
            for (Row srcRow : srcSheet) {
                Row destRow = destSheet.createRow(srcRow.getRowNum());
                copyRowStyles(src, dest, srcRow, destRow);
            }
        }
    }

    // 复制列宽
    private static void copyColumnWidths(Sheet srcSheet, Sheet destSheet) {
        for (int i = 0; i < 256; i++) {
            int width = srcSheet.getColumnWidth(i);
            if (width > 0) {
                destSheet.setColumnWidth(i, width);
            }
        }
    }

    // 复制合并单元格区域
    private static void copyMergedRegions(Sheet srcSheet, Sheet destSheet) {
        for (int i = 0; i < srcSheet.getNumMergedRegions(); i++) {
            destSheet.addMergedRegion(srcSheet.getMergedRegion(i));
        }
    }

    // 复制行和单元格（含样式）
    private static void copyRowStyles(Workbook src, Workbook dest, Row srcRow, Row destRow) {
        destRow.setHeight(srcRow.getHeight());
        for (Cell srcCell : srcRow) {
            Cell destCell = destRow.createCell(srcCell.getColumnIndex());
            copyCellStyle(src, dest, srcCell, destCell);
            copyCellValue(srcCell, destCell);
        }
    }

    // 复制单元格样式
    private static void copyCellStyle(Workbook src, Workbook dest, Cell srcCell, Cell destCell) {
        CellStyle srcStyle = srcCell.getCellStyle();
        CellStyle destStyle = dest.createCellStyle();

        // 复制基础样式


        destStyle.setAlignment(srcStyle.getAlignment());
        destStyle.setBorderBottom(srcStyle.getBorderBottom());
        destStyle.setBorderLeft(srcStyle.getBorderLeft());
        destStyle.setBorderTop(srcStyle.getBorderTop());
        destStyle.setBorderRight(srcStyle.getBorderRight());
        destStyle.setBottomBorderColor(srcStyle.getBottomBorderColor());
        destStyle.setDataFormat(srcStyle.getDataFormat());
        destStyle.setFillPattern(srcStyle.getFillPattern());
        destStyle.setHidden(srcStyle.getHidden());
        destStyle.setIndention(srcStyle.getIndention());
        destStyle.setLeftBorderColor(srcStyle.getLeftBorderColor());
        destStyle.setLocked(srcStyle.getLocked());
        destStyle.setQuotePrefixed(srcStyle.getQuotePrefixed());
        destStyle.setRightBorderColor(srcStyle.getRightBorderColor());
        destStyle.setRotation(srcStyle.getRotation());
        destStyle.setShrinkToFit(srcStyle.getShrinkToFit());
        destStyle.setTopBorderColor(srcStyle.getTopBorderColor());
        destStyle.setVerticalAlignment(srcStyle.getVerticalAlignment());
        destStyle.setWrapText(srcStyle.getWrapText());

        // TODO 原有方法可能不适用，但现有方法完全无用，尝试调整 使用增强的颜色保留方法
        ColorPreservingConverter.copyCellStyleWithColor(src, dest, srcCell, destCell);

        // 处理字体（跨工作簿需重新创建）
        Font srcFont = src.getFontAt(srcStyle.getFontIndex());
        Font destFont = dest.findFont(
                srcFont.getBold(),
                srcFont.getColor(),
                srcFont.getFontHeight(),
                srcFont.getFontName(),
                srcFont.getItalic(),
                srcFont.getStrikeout(),
                srcFont.getTypeOffset(),
                srcFont.getUnderline()
        );
        if (destFont == null) {
            destFont = dest.createFont();
            copyFontProperties(srcFont, destFont);
        }
        destStyle.setFont(destFont);
        destCell.setCellStyle(destStyle);
    }

    // 复制字体属性
    private static void copyFontProperties(Font src, Font dest) {
        dest.setBold(src.getBold());
        dest.setColor(src.getColor());
        dest.setFontHeight(src.getFontHeight());
        dest.setFontName(src.getFontName());
        dest.setItalic(src.getItalic());
        dest.setStrikeout(src.getStrikeout());
        dest.setTypeOffset(src.getTypeOffset());
        dest.setUnderline(src.getUnderline());
    }

    // 复制单元格值
    private static void copyCellValue(Cell srcCell, Cell destCell) {
        switch (srcCell.getCellType()) {
            case STRING:
                destCell.setCellValue(srcCell.getStringCellValue());
                break;
            case NUMERIC:
                destCell.setCellValue(srcCell.getNumericCellValue());
                break;
            case BOOLEAN:
                destCell.setCellValue(srcCell.getBooleanCellValue());
                break;
            case FORMULA:
                destCell.setCellFormula(srcCell.getCellFormula());
                break;
            case BLANK:
                destCell.setBlank();
                break;
            default:
                destCell.setCellValue("");
        }
    }
}